package batch.topseller;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Date;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.lang.ObjectUtils;
import org.apache.commons.lang.StringEscapeUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.log4j.Logger;

import batch.common.db.ConnectionManager;

public class TopSellerGbr {
	
	final String startDay 	= DateFormatUtils.format(addDays(new java.util.Date(), -7), "yyyyMMdd");
	final String endDay 	= DateFormatUtils.format(new Date(), "yyyyMMdd");
	private StringBuffer  strSql       = new StringBuffer();
	private QueryRunner   qRunner;
	
	public TopSellerGbr(){
		
	}
	
	public static void main(String [] args) {
        Logger.getLogger("topseller.TopSellerGbr").info("( * ) Start.");

        TopSellerGbr topsell = new TopSellerGbr();
        topsell.init("002"); //Screensaver
        topsell.init("003"); //Wallpaper
        topsell.init("005"); //Video

        Logger.getLogger("topseller.TopSellerGbr").info("( * ) End.");
    }
	
	public void init(String strCtype_cd) {		
        StringBuffer sQuery = new StringBuffer()
			        .append("		SELECT * FROM																				\n")												
					.append("		(                                                                                           \n")
					.append("			SELECT                                                                                  \n")
					.append("			ROW_NUMBER() OVER(ORDER BY count(*) DESC) NUM                                           \n")
					.append("			,W.CONTENT_ID CONTENTS_ID                                                               \n")
					.append("			, A.DDOCTITLE TITLE_NM                                                                  \n")
					.append("			, B.XWAP_PRE1 PREVIEW_FILE_NM                                                           \n")
					.append("			, TRIM(TO_CHAR(B.XPRICE, '9990.00')) PRICE                                              \n")
					.append("			, TO_CHAR(B.XCP_ID) CP_ID                                                               \n")
					.append("			, COUNT(*) DOWN_CNT                                                                     \n")
					.append("			FROM G2_DOWN_LOG_GBR W                                                                  \n")
					.append("			,                                                                                       \n")
					.append("			(                                                                                       \n")
					.append("			SELECT                                                                                  \n")
					.append("				CONTENT_ID, CTYPE_CD                                                                \n")
					.append("				FROM G2_GENRE_CONTENT                                                               \n")
        			.append("	    		WHERE GENRE_ID IN (                													\n")
					.append("									select genre_id                									\n")
					.append("									from g2_genre                									\n")
					.append("									where ctype_cd = '").append(strCtype_cd).append("'				\n")
					.append("									and cntry_cd = '0000064'                						\n")
					.append("									and content_flag = '001'                						\n")
					.append("									and use_cd = '1'                								\n")
					.append("									and webwap_flag_cd <> '003')                					\n")
					.append("				AND CNTRY_CD = '0000064'															\n")
					.append("				GROUP BY CONTENT_ID, CTYPE_CD                                                       \n")
					.append("			) T2                                                                                    \n")
					.append("			, REVISIONS A                                                                           \n")
					.append("			, DOCMETA B                                                                             \n")
					.append("			, G2_CP C                                                                               \n")
					.append("			WHERE W.CONTENT_ID = T2.CONTENT_ID                                                      \n")
					.append("			AND W.CTYPE_CD = T2.CTYPE_CD                                                            \n")
					.append("			AND T2.CONTENT_ID = A.DDOCNAME                                                          \n")
					.append("			AND A.DID = B.DID                                                                       \n")
					.append("			AND B.XCP_ID = C.CP_ID                                                                  \n")
					.append("			AND W.CREATED_YMDHMS >= '"+startDay+"000000' AND W.CREATED_YMDHMS < '"+endDay+"000000'  \n")
					.append("			AND W.DOWN_STATUS = '001'                                                               \n")
					.append("			AND W.CTYPE_CD = '"+strCtype_cd+"'                                                      \n")
					.append("			AND B.XWEBWAP_FLAG_CD <> '003'                                                          \n")
					.append("			AND B.XCP_ID <> 1                                                                       \n")
					.append("			AND B.XCONTENT_FLAG = '001'                                                             \n")
					.append("			AND B.XSTATUS = '002'                                                                   \n")
					.append("			AND C.USE_CD = '1'                                                                      \n")
					.append("			GROUP BY W.CONTENT_ID, A.DDOCTITLE, B.XWAP_PRE1, B.XPRICE, B.XCP_ID                     \n")
					.append("			)                                                                                       \n")
					.append("			X                                                                                       \n")
					.append("		WHERE X.NUM BETWEEN 1 AND 100                                                               \n");
		
		String num				= "";
		String contents_id 	    = "";
		String title_nm 	    = "";
		String preview_file_nm	= "";	
		String price			= "";
		String cp_id 			= "";
		String scate_cd 		= "";			
		String down_cnt		    = "";	
        
        Connection conn   = null;
        Statement  stmt   = null;
        ResultSet  rs     = null;
      
        boolean strResult      = false;
        int    i = 0;

        try{
        	conn = ConnectionManager.getConnection();
            conn.setAutoCommit(true);
            stmt= conn.createStatement();
            
            rs = stmt.executeQuery(sQuery.toString());            
            
            strResult = deleteData(conn, strCtype_cd );            
            
            QueryRunner qRunner = new QueryRunner();
            
            //Logger.getLogger("topseller.TopSellerGbr").info(sQuery.toString());
            while (rs.next()) {
                i++;
                
                num       		= StringUtils.defaultString(rs.getString(1));
        		contents_id   	= ObjectUtils.toString(rs.getObject(2));
        		title_nm        = StringEscapeUtils.escapeSql(ObjectUtils.toString(rs.getObject(3)));
        		preview_file_nm = ObjectUtils.toString(rs.getObject(4));
                price 			= ObjectUtils.toString(rs.getObject(5));
                cp_id  			= ObjectUtils.toString(rs.getObject(6));                
                down_cnt		= ObjectUtils.toString(rs.getObject(7));
                
                StringBuffer sQuerySelect = new StringBuffer()
                	.append("SELECT GENRE_ID FROM G2_GENRE_CONTENT	WHERE CONTENT_ID='"+contents_id+"' AND ROWNUM = 1\n");
                
                ResultSetHandler rsh = new ScalarHandler(1);
                scate_cd = StringUtils.defaultString((String)qRunner.query(conn, sQuerySelect.toString(), rsh));
                
                StringBuffer sQueryInsert = new StringBuffer()
                    		.append("INSERT INTO G_GBR_IMAGE_TOPSELLER( NUM, CONTENTS_ID, TITLE_NM, PREVIEW_FILE_NM, PRICE, CP_ID, SCATE_CD, CTYPE_CD, DOWN_CNT)  \n")
                        	.append(" VALUES              \n")                        	
                    		.append("('"+num+"', '"+contents_id+"', '"+title_nm+"', '"+preview_file_nm+"', '"+price+"', "+cp_id+", '"+scate_cd+"', '"+strCtype_cd+"', '"+down_cnt+"'	)\n");
                    
                qRunner.update(conn, sQueryInsert.toString());

                if( (i%100) == 0){
                    Logger.getLogger("topseller.TopSellerGbr").info("here we are == " + i);
                }
            }
        }
        catch(Exception ex){
            ex.printStackTrace();
            Logger.getLogger("topseller.TopSellerGbr").info(ex.toString());
        }
        finally{
            if(rs        != null) try { rs.close();        } catch(Exception se) {}
            if(stmt      != null) try { stmt.close();      } catch(Exception se) {}
            if(conn      != null) try { conn.close();      } catch(Exception se) {}
        }
    }
	
	public boolean  deleteData(Connection conn, String CTYPE_CD ) throws Exception{
        int iRE = 0;
        boolean flag = false;
        strSql.setLength(0);

        try{
            qRunner = new QueryRunner();

            strSql.append("DELETE FROM G_GBR_IMAGE_TOPSELLER 			\n")
                  .append(" WHERE CTYPE_CD    = '" + CTYPE_CD + "'      \n");
            
            iRE = qRunner.update(conn, strSql.toString());
            if(iRE==1){
            	flag = true;
            }
        }
        finally{
        }
        return flag;
    }
	
	
	public java.util.Date addDays(Date date, int day)
    {
		date.setTime(date.getTime() + ((long)day * 1000 * 60 * 60 * 24));
        return date;
    }  
	
}
